<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*"%>
				<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");

						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							// Begin transaction
							conn.setAutoCommit(false);
							//System.out.println("Insert!");
							PreparedStatement pstmt = conn.prepareStatement(
					                "INSERT INTO class VALUES (?, ?, ?, ?)");
							pstmt.setString(1, request.getParameter("course_number"));    //course_number
							pstmt.setString(2, request.getParameter("title"));   //title
							pstmt.setString(3, request.getParameter("quarter"));    //quarter		
							pstmt.setInt(4, Integer.parseInt(request.getParameter("year")));   //year				
					        pstmt.executeUpdate();  
					        			        
							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
						}
						
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // UPDATE the course attributes in the course table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "UPDATE class SET title = ?, quarter = ?, year = ? WHERE class_id = ?");

	                        pstmt.setString(1, request.getParameter("title"));   //title
							pstmt.setString(2, request.getParameter("quarter"));    //quarter		
							pstmt.setInt(3, Integer.parseInt(request.getParameter("year")));   //year	
							pstmt.setInt(4, Integer.parseInt(request.getParameter("class_id"))); 
							
	                        pstmt.executeUpdate();
	                        
	                        pstmt = conn.prepareStatement("DELETE FROM concentration_course WHERE concentration_id = ?");
	                     	pstmt.setString(1, request.getParameter("concentration_id"));
	                     	pstmt.executeUpdate();

	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						if (action != null && action.equals("delete")) {
							
	                        conn.setAutoCommit(false);
	                        
	                        PreparedStatement pstmt = conn.prepareStatement(
		                        "SELECT section_id FROM section WHERE class_id = ?");
		                    pstmt.setInt(1, Integer.parseInt(request.getParameter("class_id")));
		                   	ResultSet rs = pstmt.executeQuery();
		                      	
	                        while(rs.next()){
	                        	int section_id = rs.getInt("section_id");
	                      	//delete meeting
	                      	pstmt = conn.prepareStatement(
	                            "DELETE FROM meeting WHERE section_id = ?");
	                      	pstmt.setInt(1, section_id);
	                      	pstmt.executeUpdate();
	                      	
	                      	//delete review session
	                      	pstmt = conn.prepareStatement(
	                            "DELETE FROM review_session WHERE section_id = ?");
	                      	pstmt.setInt(1, section_id);
	                      	pstmt.executeUpdate();
	                      	
	                      	//delete enroll
	                      	pstmt = conn.prepareStatement(
	                            "DELETE FROM enroll WHERE section_id = ?");
	                      	pstmt.setInt(1, section_id);
	                      	pstmt.executeUpdate();
	                      	
	                      	//delete section
	                      	pstmt = conn.prepareStatement(
	                            "DELETE FROM section WHERE section_id = ?");
	                      	pstmt.setInt(1, section_id);
	                      	pstmt.executeUpdate();
	                        }
	                           
	                        //delete class
	                        pstmt = conn.prepareStatement(
	                            "DELETE FROM class WHERE class_id = ?");
	                      	pstmt.setInt(1, Integer.parseInt(request.getParameter("class_id")));
	                      	pstmt.executeUpdate();
	                        
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
					
						// Create the statement
						Statement statement = conn.createStatement();
						//ResultSet requirement = statement.executeQuery("SELECT * FROM degree_requirement WHERE title IN ('MS','PhD')");
						//CachedRowSet req = new CachedRowSetImpl();
						//req.populate(requirement);
						
						//obtain all the courses
						ResultSet rsCourse = statement.executeQuery("SELECT course_number " +
								"FROM course ORDER BY course_number");
						List<String> courses = new ArrayList<String>();
						while (rsCourse.next()) {
							courses.add(rsCourse.getString("course_number"));
						}
						
						//obtain the current class information
						//statement = conn.createStatement();
						ResultSet concentration = statement.executeQuery("SELECT * FROM class");
						CachedRowSet rs = new CachedRowSetImpl();
						rs.populate(concentration);
						
				%>
				<table border="1">
					<tr>
						<th>Course Number</th>	
						<th>Class Title</th>
						<th>Quarter</th>
						<th>Year</th>
					</tr>
					<tr>
						<form action="class.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							
							<th>
							<select name="course_number" style="width:100px">	
							<%	
								
								for(String course:courses){
							%>
								<option value="<%=course %>"><%=course%></option>
							<%
								}
							%>
							</select>
							</th>
							<th><input type="text" value="" name="title" size="20"></th>
							<th>
								<select name="quarter" style="width:100px">
								<option value="Spring">Spring</option>
								<option value="Summer">Summer</option>
								<option value="Fall">Fall</option>
								<option value="Winter">Winter</option>
								</select>
							</th>		
							<th><input type="text" value="" name="year" size="10"></th>		
							<th><input type="submit" value="Insert"></th>
						</form>
					</tr>
								<% 
                        			while(rs.next()){		
                            	%> 
                            	<tr>	
                        				<form action="class.jsp" method="POST">
                           				<input type="hidden" value="update" name="action">                        
                            		
                            			<td><input type="hidden" value="<%=rs.getInt("class_id")%>" name="class_id">
                            			<%= rs.getString("course_number") %></td>
                            			<td><input type="text" value="<%= rs.getString("title")%>" name="title"></td>
                            			<td>
                            			<select name="quarter"  style="width:100px">
											<option value="Spring" <%if(rs.getString("quarter").equals("Spring")) out.println("selected='selected'");%>>Spring</option>
											<option value="Summer" <%if(rs.getString("quarter").equals("Summer")) out.println("selected='selected'");%>>Summer</option>
											<option value="Fall" <%if(rs.getString("quarter").equals("Fall")) out.println("selected='selected'");%>>Fall</option>
											<option value="Winter" <%if(rs.getString("quarter").equals("Winter")) out.println("selected='selected'");%>>Winter</option>
										</select>
                            			</td>
                            			<td>  <input type="text" value="<%= rs.getString("year")%>" name="year" size="10"></td>
                            			<td>
                                			<input type="submit" value="Update">
                            			</td>
                        				</form>
                        				<td>
                       					<form action="class.jsp" method="POST">
                           					 <input type="hidden" value="delete" name="action">
                          					 <input type="hidden" value="<%= rs.getInt("class_id") %>" name="class_id">                       			
                               				 <input type="submit" value="Delete">
                               			</form>
                                		</td>
                    			</tr>
                    			<% 
                            	}
                            	%>
					
					<%
						} catch (SQLException e) {
							System.out.println(e.getMessage());
							//conn.rollback();
						} catch (Exception e) {
							System.out.println(e.getMessage());
							//conn.rollback();
						}
					%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>



